package dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;

import util.DBHelper;
import orm.TeachingPlanBaseInfo;

public class TeachingPlanBaseInfoDAO {
	
	
	public ArrayList<TeachingPlanBaseInfo> getTPByUserName(int teacherID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select distinct TPID, ActivityName, " +
				"(select TName from teacher where teacherID = " + teacherID + ") " +
				"as TeacherName, EditingTimes, TPType, Constitutor " +
				" from teachingplanbaseinfo where teacherID = " + teacherID + " " );
				
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setTeacherName(rs.getString(3));
			tpbi.setEditingTimes(rs.getInt(4));
			tpbi.setTpType(rs.getInt(5));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getCollectTPByUserName(int teacherID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;

		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct tpbi.TPID,tpbi.ActivityName,t.tname,tpbi.EditingTimes, tpbi.TPType ");
		sqlStr.append("from teachingplanbaseinfo tpbi left join teacher as t on tpbi.teacherId = t.teacherId ");
		sqlStr.append("where tpbi.tpid in ");
		sqlStr.append("(select distinct tpid from usercollection where teacherID = " + teacherID + "); ");

		
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setTeacherName(rs.getString(3));
			tpbi.setEditingTimes(rs.getInt(4));
			tpbi.setTpType(rs.getInt(5));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getShareTP() throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select tpbi.TPID, tpbi.ActivityName,t.TName, tpbi.EditingTimes, tpbi.TPType ");
		sqlStr.append(" from teachingplanbaseinfo tpbi inner join teacher as t on ");
		sqlStr.append(" tpbi.teacherId = t.teacherId ");
		sqlStr.append(" where Share = 1 ");
		sqlStr.append(" ORDER BY TPID ASC ");
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		
		
		ArrayList<TeachingPlanBaseInfo> tpbiArr = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setTeacherName(rs.getString(3));
			tpbi.setEditingTimes(rs.getInt(4));
			tpbi.setTpType(rs.getInt(5));
			
			tpbiArr.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return tpbiArr;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getSearchCondition(Integer tpType,Integer tpProperty,
			Integer appropriateAge,String monthThemeName,String weekThemeName,Integer developmentDomain,
			Integer aggrandizementDomain,Integer activityType,String constitutor,String tpYear) throws SQLException {
		
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct tpbi.TPID, tpbi.ActivityName,t.TName, tpbi.EditingTimes, tpbi.TPType ");
		sqlStr.append("from teachingplanbaseinfo tpbi left join collectivityteachingplan ctp on tpbi.TPID=ctp.TPID ");
		sqlStr.append("inner join teacher t on tpbi.TeacherID=t.TeacherID ");
		sqlStr.append("left join individualteachingplan itp on tpbi.TPID=itp.TPID ");
		sqlStr.append("where tpbi.Share = 1 ");
		if(tpType!=0){
			sqlStr.append(" and tpbi.TPType = " + tpType);
		}
		if(tpProperty!=0){
			sqlStr.append(" and tpbi.TPProperty = " + tpProperty);
		}
		if(appropriateAge!=0){
			sqlStr.append(" and tpbi.AppropriateAge = " + appropriateAge);
		}
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and tpbi.MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(weekThemeName!=null && !weekThemeName.equals("")){
			sqlStr.append(" and tpbi.WeekThemeName like '%" + weekThemeName + "%'");
		}
		if(developmentDomain!=0){
			sqlStr.append(" and ctp.DevelopmentDomain = " + developmentDomain);
		}
		if(aggrandizementDomain!=0){
			sqlStr.append(" and itp.AggrandizementDomain = " + aggrandizementDomain);
		}
		if(activityType!=0){
			sqlStr.append(" and tpbi.ActivityType = " + activityType);
		}
		if(constitutor!=null && !constitutor.equals("")){
			sqlStr.append(" and t.TeacherName like '" + constitutor + "%'");
		}
		if(tpYear!=null && !tpYear.equals("")){
			sqlStr.append(" and tpbi.ExecutionDate like '%" + tpYear + "%'");
		}
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		
		
		ArrayList<TeachingPlanBaseInfo> tpbiArr = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setTeacherName(rs.getString(3));
			tpbi.setEditingTimes(rs.getInt(4));
			tpbi.setTpType(rs.getInt(5));
			
			tpbiArr.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return tpbiArr;
	}
	
	public int getTemplateUsedTimes(String tpid) throws SQLException{
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		stmt = conn.createStatement();

		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select count(tpid) from teachingplanbaseinfo where tpid like '"+tpid+"%'");
		rs = stmt.executeQuery(sqlStr.toString());
		
		while(rs.next()){
			return rs.getInt(1);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return 0;
	}
	
	public void shareTPByTPID(String tpid) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		

		stmt = conn.createStatement();
		stmt.execute("update teachingplanbaseinfo set Share=1 where TPID='" + tpid + "'");
		
		stmt.close();
		conn.close();
	}
	
	public boolean selectShareByTPID(String tpid) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select count(*) from teachingplanbaseinfo where Share=1 and tpid = '" + tpid + "'");
		rs.next();
		
		boolean flag = rs.getInt(1) == 0 ? false : true;
		rs.close();
		stmt.close();
		conn.close();
		return flag;

	}
	
	public void disshareTPByTPID(String tpid) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		
		stmt = conn.createStatement();
		stmt.execute("update teachingplanbaseinfo set Share=0 where TPID='" + tpid + "'");
		stmt.close();
		conn.close();
	}
	
	public TeachingPlanBaseInfo getTPByTPID(String tpID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from teachingplanbaseinfo where TPID = '" + tpID + "'");
		
		TeachingPlanBaseInfo tpbi = new TeachingPlanBaseInfo();
		rs.next();
		
		int i = 1;
		tpbi.setTpID(rs.getString(i++));
		tpbi.setTpMaterial(rs.getInt(i++));
		tpbi.setTpProperty(rs.getInt(i++));
		tpbi.setTpType(rs.getInt(i++));
		tpbi.setConstitutor(rs.getString(i++));
		tpbi.setTeacherID(rs.getInt(i++));
		tpbi.setWroteDate(rs.getString(i++).substring(0, 10));
		tpbi.setExecutionDate(rs.getString(i++).substring(0, 10));
		tpbi.setAppropriateAge(rs.getInt(i++));
		tpbi.setAppropriateStudents(rs.getString(i++));
		tpbi.setActivityName(rs.getString(i++));
		tpbi.setMonthThemeName(rs.getString(i++));
		tpbi.setWeekThemeName(rs.getString(i++));
		tpbi.setActivityType(rs.getInt(i++));
		tpbi.setShare(rs.getInt(i++));
		tpbi.setGrade(rs.getInt(i++));
		tpbi.setEditingTimes(rs.getInt(i++));
		
		rs.close();
		stmt.close();
		conn.close();
		return tpbi;
	}
	
	public void updateTPByTPID(Statement statement, TeachingPlanBaseInfo tpbi) throws SQLException {
		statement.execute("update teachingplanbaseinfo set TPProperty = " + tpbi.getTpProperty() + 
				", WroteDate = '" + tpbi.getWroteDate() + "', ExecutionDate = '" + tpbi.getExecutionDate() + 
				"', AppropriateAge = " + tpbi.getAppropriateAge() + ", AppropriateStudents = '" + 
				tpbi.getAppropriateStudents() + "', ActivityName = '" + tpbi.getActivityName() + 
				"', MonthThemeName = '" + tpbi.getMonthThemeName() + "', WeekThemeName = '" + tpbi.getWeekThemeName() + 
				"', ActivityType = " + tpbi.getActivityType() + " where TPID = '" + tpbi.getTpID() + "'");
		
	}
	
	public void insertTeachingPlanBaseInfo(Statement statement, TeachingPlanBaseInfo tpbi) throws SQLException {
		statement.execute("insert into teachingplanbaseinfo values('" + tpbi.getTpID() + "', " + tpbi.getTpMaterial() + 
				", " + tpbi.getTpProperty() + ", " + tpbi.getTpType() + ", '" + tpbi.getConstitutor() + 
				"','" + tpbi.getTeacherID() + "', '" + tpbi.getWroteDate() + "', '" + tpbi.getExecutionDate() + "', " + tpbi.getAppropriateAge() +
				", '" + tpbi.getAppropriateStudents() + "', '" + tpbi.getActivityName() + "', '" + tpbi.getMonthThemeName() + 
				"', '" + tpbi.getWeekThemeName() + "', " + tpbi.getActivityType() +
				", " + tpbi.isShare() + ", " + tpbi.getGrade() + ", " + tpbi.getEditingTimes() + ")");
	}
	
	public void deleteTPByTPID(Statement statement, String tpid) throws SQLException {
		statement.execute("delete from teachingplanbaseinfo where TPID='" + tpid +"'");
	}
	
	public ArrayList<TeachingPlanBaseInfo> getMonthThemeNameByTPProperty() throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select distinct MonthThemeName from teachingplanbaseinfo where TPProperty = 1");
		
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
//			int i = 1;
			tpbi.setMonthThemeName(rs.getString(1));
			
			al.add(tpbi);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getWeekThemeNameByTPProperty() throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select distinct WeekThemeName from teachingplanbaseinfo where TPProperty = 1");
		
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			int i = 1;
			tpbi.setWeekThemeName(rs.getString(i++));
			
			al.add(tpbi);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getCTPIDByExecutionDate(String executionDate, int teacherID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		String[] dataParts = executionDate.split("-");
		Calendar c = Calendar.getInstance();
		c.set(new Integer(dataParts[0]), new Integer(dataParts[1])-1, new Integer(dataParts[2]));
		c.add(Calendar.DAY_OF_MONTH, 4);
		String friday = c.get(Calendar.YEAR)+"-"+(c.get(Calendar.MONTH)+1)+"-"+c.get(Calendar.DAY_OF_MONTH);
		
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct TPID, ActivityName,ExecutionDate ");
		sqlStr.append(" from teachingplanbaseinfo ");
		sqlStr.append(" where teacherID = " + teacherID + " and TPType = 2 and ExecutionDate >= '" + executionDate + "' and ExecutionDate<='"+friday+"' order by ExecutionDate asc");		
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setExecutionDate(rs.getString(3));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getICTPIDByExecutionDate(String executionDate, int teacherID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		String[] dataParts = executionDate.split("-");
		Calendar c = Calendar.getInstance();
		c.set(new Integer(dataParts[0]), new Integer(dataParts[1])-1, new Integer(dataParts[2]));
		c.add(Calendar.DAY_OF_MONTH, 4);
		String friday = c.get(Calendar.YEAR)+"-"+(c.get(Calendar.MONTH)+1)+"-"+c.get(Calendar.DAY_OF_MONTH);
		
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct tpbi.TPID, tpbi.ActivityName,tpbi.ExecutionDate, itp.CorePurpose ");
		sqlStr.append("from teachingplanbaseinfo tpbi left join individualteachingplan itp on tpbi.TPID = itp.TPID ");
		sqlStr.append("where tpbi.teacherID = " + teacherID + " and tpbi.TPType = 1 and tpbi.ExecutionDate >= '" + executionDate + "' and tpbi.ExecutionDate<='"+friday+"' order by tpbi.ExecutionDate asc");
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setExecutionDate(rs.getString(3));
			tpbi.setCorePurpose(rs.getInt(4));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getActivityNameByWeekThemeName(String WeekThemeName, int teacherID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct TPID, ActivityName ");
		sqlStr.append(" from teachingplanbaseinfo ");
		sqlStr.append(" where teacherID = " + teacherID + " and TPProperty =1 and TPType = 2 and  WeekThemeName = '" + WeekThemeName + "' ");		
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getWeekThemeNameByMonthThemeName(String monthThemeName) throws SQLException{
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct WeekThemeName from teachingplanbaseinfo where MonthThemeName = '" + monthThemeName + "'");
		rs = stmt.executeQuery(sqlStr.toString());
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setWeekThemeName(rs.getString(1));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
		
	}
	
	public String getThemeActivityNameByTPID(String tpid) throws SQLException{
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;

		
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select ActivityName from teachingplanbaseinfo where TPID = '" + tpid + "'");
		rs = stmt.executeQuery(sqlStr.toString());
			
		while(rs.next()){
			return rs.getString(1);
		}
		rs.close();
		stmt.close();
		conn.close();
		return null;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getMySearchCondition(Integer tpType,Integer tpProperty,
			Integer appropriateAge,String monthThemeName,String weekThemeName,Integer developmentDomain,
			Integer aggrandizementDomain,Integer activityType,int teacherID,String tpYear) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;

		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select tpbi.TPID,tpbi.ActivityName, ");
		sqlStr.append("(select TName from teacher where teacherID = " + teacherID + ") as TName, ");
		sqlStr.append("tpbi.EditingTimes, tpbi.TPType, tpbi.Constitutor ");
		sqlStr.append("from teachingplanbaseinfo tpbi left join collectivityteachingplan ctp on tpbi.TPID=ctp.TPID ");
		sqlStr.append("left join teacher t on tpbi.TeacherID=t.TeacherID ");
		sqlStr.append("left join individualteachingplan itp on tpbi.TPID=itp.TPID ");
		sqlStr.append("where tpbi.TeacherID = " + teacherID + " ");
		if(tpType!=0){
			sqlStr.append(" and tpbi.TPType = " + tpType);
		}
		if(tpProperty!=0){
			sqlStr.append(" and tpbi.TPProperty = " + tpProperty);
		}
		if(appropriateAge!=0){
			sqlStr.append(" and tpbi.AppropriateAge = " + appropriateAge);
		}
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and tpbi.MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(weekThemeName!=null && !weekThemeName.equals("")){
			sqlStr.append(" and tpbi.WeekThemeName like '%" + weekThemeName + "%'");
		}
		if(developmentDomain!=0){
			sqlStr.append(" and ctp.DevelopmentDomain = " + developmentDomain);
		}
		if(aggrandizementDomain!=0){
			sqlStr.append(" and itp.AggrandizementDomain = " + aggrandizementDomain);
		}
		if(activityType!=0){
			sqlStr.append(" and tpbi.ActivityType = " + activityType);
		}
		if(tpYear!=null && !tpYear.equals("")){
			sqlStr.append(" and tpbi.ExecutionDate like '%" + tpYear + "%'");
		}
		
		rs = stmt.executeQuery(sqlStr.toString());
		
		
		
		ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
		TeachingPlanBaseInfo tpbi = null;
		while (rs.next()) {
			tpbi = new TeachingPlanBaseInfo();
			tpbi.setTpID(rs.getString(1));
			tpbi.setActivityName(rs.getString(2));
			tpbi.setTeacherName(rs.getString(3));
			tpbi.setEditingTimes(rs.getInt(4));
			tpbi.setTpType(rs.getInt(5));
			
			al.add(tpbi);
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return al;
	}
	
	public ArrayList<TeachingPlanBaseInfo> getColSearchCondition(Integer tpType,Integer tpProperty,
			Integer appropriateAge,String monthThemeName,String weekThemeName,Integer developmentDomain,
			Integer aggrandizementDomain,Integer activityType,int teacherID,String coltpYear) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		stmt = conn.createStatement();
		
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select distinct tpbi.TPID,tpbi.ActivityName, t.tname as TeacherName,tpbi.EditingTimes,tpbi.TPType ");
		sqlStr.append("from teachingplanbaseinfo tpbi left join collectivityteachingplan ctp on tpbi.TPID=ctp.TPID ");
		sqlStr.append("left join teacher t on tpbi.TeacherID=t.TeacherID ");
		sqlStr.append("left join individualteachingplan itp on tpbi.TPID=itp.TPID ");
		sqlStr.append("where tpbi.tpid in (select distinct uc.tpid from usercollection uc where teacherID = " + teacherID + ") ");
		
		if(tpType!=0){
			sqlStr.append(" and tpbi.TPType = " + tpType);
		}
		if(tpProperty!=0){
			sqlStr.append(" and tpbi.TPProperty = " + tpProperty);
		}
		if(appropriateAge!=0){
			sqlStr.append(" and tpbi.AppropriateAge = " + appropriateAge);
		}
		if(monthThemeName!=null && !monthThemeName.equals("")){
			sqlStr.append(" and tpbi.MonthThemeName like '%" + monthThemeName + "%'");
		}
		if(weekThemeName!=null && !weekThemeName.equals("")){
			sqlStr.append(" and tpbi.WeekThemeName like '%" + weekThemeName + "%'");
		}
		if(developmentDomain!=0){
			sqlStr.append(" and ctp.DevelopmentDomain = " + developmentDomain);
		}
		if(aggrandizementDomain!=0){
			sqlStr.append(" and itp.AggrandizementDomain = " + aggrandizementDomain);
		}
		if(activityType!=0){
			sqlStr.append(" and tpbi.ActivityType = " + activityType);
		}
		if(coltpYear!=null && !coltpYear.equals("")){
			sqlStr.append(" and tpbi.ExecutionDate like '%" + coltpYear +"%'");
		}
		
		rs = stmt.executeQuery(sqlStr.toString());
			
			ArrayList<TeachingPlanBaseInfo> al = new ArrayList<TeachingPlanBaseInfo>();
			TeachingPlanBaseInfo tpbi = null;
			while (rs.next()) {
				tpbi = new TeachingPlanBaseInfo();
				tpbi.setTpID(rs.getString(1));
				tpbi.setActivityName(rs.getString(2));
				tpbi.setTeacherName(rs.getString(3));
				tpbi.setEditingTimes(rs.getInt(4));
				tpbi.setTpType(rs.getInt(5));
				
				al.add(tpbi);
			}
			
			rs.close();
			stmt.close();
			conn.close();
			return al;
	}
	
}
